package model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Map;

import javax.naming.NamingException;

import db.DbConnectionPool;
import db.DbException;

import forms.DegreeFormInsert;

public class DegreeModel {
	private static String insertStr = "INSERT INTO Degrees (university,discipline,month,year,gpa,titleofdegree,students) VALUES (?,?,?,?,?,?,?)";
	
	public static void insertDegrees(List<DegreeFormInsert> degrees) throws DbException{
		try {
		
		Connection conn = DbConnectionPool.getConnection();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		conn.setAutoCommit(false);
		

        Statement stmt = conn.createStatement();
        rs = stmt.executeQuery("SELECT id FROM Students");
        int CurrentID =0;
        while(rs.next())
        	CurrentID = rs.getInt(1);                  	
        stmt.close();
        
        for(DegreeFormInsert d : degrees) {
        	pstmt = conn.prepareStatement("SELECT id FROM Universities WHERE location=? AND name =? ");
        	pstmt.setString(1,d.getLocation());
        	System.out.println(d.getLocation());
        	pstmt.setString(2,d.getUniversity());
        	System.out.println(d.getUniversity());
        	rs = pstmt.executeQuery();
        	
        	int university_id = -1;
        	if(rs.next()) {
        		university_id = rs.getInt(1);
        		System.out.println(rs.getInt(1));
        	}
        	else
        		System.out.println("univ error");
        	
        	pstmt = conn.prepareStatement("SELECT id FROM Discipline WHERE name=?");
            pstmt.setString(1,d.getDiscipline());
            rs = pstmt.executeQuery();
            rs.next();
           	int descipline_id = rs.getInt(1);
           	
           	
           	pstmt = conn.prepareStatement(insertStr);
            
            pstmt.setInt(1, university_id);
            pstmt.setInt(2, descipline_id);
            pstmt.setInt(3, Integer.parseInt(d.getMonth()));
            pstmt.setInt(4, Integer.parseInt(d.getYear()));
            pstmt.setFloat(5, Float.parseFloat(d.getGpa()));		
            pstmt.setString(6, d.getTitleofdegree());
            pstmt.setInt(7, CurrentID); 
 
			pstmt.executeUpdate();

			conn.commit();    
           	
        	
		}
        
 
		
		pstmt.close();
        conn.setAutoCommit(true);
        conn.close();
		
		
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}


}
}

